This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query.

Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I am usually asked if there is any way one can know how much data in a table is stored in the memory cache? The more detailed question I usually get is if there are multiple indexes on table (and used in a query), were the data of the single table stored multiple times in the memory cache or only for a single time?

Here is a query you can run to figure out what kind of data is stored in the cache.

USE AdventureWorks
GO
SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO


Now let us run the query above and observe the output of the same.

We can see in the above query that there are four columns.

Cached_Pages_Count lists the pages cached in the memory.
BaseTableName lists the original base table from which data pages are cached.
IndexName lists the name of the index from which pages are cached.
IndexTypeDesc lists the type of index.

Now, let us do one more experience here. Please note that you should not run this test on a production server as it can extremely reduce the performance of the database.

DBCC DROPCLEANBUFFERS

This will drop all the clean buffers and we will be able to start again from there. Now run following script and check the execution plan for the same.
USE AdventureWorks
GO
SELECT UnitPrice, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID BETWEEN 1 AND 100
GO

The execution plans contain the usage of two different indexes.

Now, let us run the script that checks the pages cached in SQL Server. It will give us the following output.

It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.

Let me know what you think of this article. I had a great pleasure while writing this article because I was able to write on this subject, which I like the most. In the next article, we will exactly see what data are cached and those that are not cached, using a few undocumented commands.

Reference: Pinal Dave (http://blog.SQLAuthority.com)